﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;
using PCSComUtils.Common;
using System.Runtime.InteropServices;
using System.Text;

namespace PCSComProduct.Items.DS
{
    public class ValueForWorkingTimeDS
    {
        
       
        public DataSet GetData(int iMaserID)
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT MaSo AS [Mã số], C1 AS Cụm#, C2 AS [Cụm#.#], Name AS [Tên chi tiết], Code AS [Ký hiệu], Levels, Parrent, DVTP, Gqty AS [Số lượng],TenQuyCachVatLieu AS [Tên quy cách vật liệu], MaVTHH, UM AS [Đơn vị], Qty AS [Slg VT], TrongLuong AS [Trọng lượng], LinhVT, m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12, m13, m14, m15, Vc_NoiBo, GhiChu, ValueForWorkingTimeMasterID, ValueForWorkingTimeDetailID FROM ITM_ValueForWorkingTimeDetail WHERE ValueForWorkingTimeMasterID='");
                strSql.Append(iMaserID);
                strSql.Append("'");
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
        //Insert Into AA
        public void RunScript(int ID)
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                StringBuilder strSql = new StringBuilder();
                //strSql.Append("SELECT * FROM ITM_ValueForWorkingTimeDetail WHERE ValueForWorkingTimeMasterID='");
                strSql.Append("drop table aa select * into aa from ITM_ValueForWorkingTimeDetail where ValueForWorkingTimeMasterID='");
                strSql.Append(ID);
                strSql.Append("'");
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                //return dstPCS;
            }
            catch (OleDbException ex)
            {
                //throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                //throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        //----- Import to ITM_PRODUCT
        public void RunScript2()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string sql = "Insert into ITM_PRODUCT (Code,Description,Revision,WorkCenterID,MakeItem,";
                sql += "SetupDate,CreateDateTime,StockUMID,SellingUMID,BuyingUMID,CCNID,PlanType,ProductionLineID,ProductTypeID,Group1,Group2,GroupQuantity,";
                sql += "Rate,DesignUMID,CategoryID)Select p.Code+'-'+TNC,p.Name,'LT',WT,1,'2007-09-02 00:00','2007-09-02 00:00',";
                sql += "36,36,36,1,0,W.ProductionLineID,1,C1,C2,Gqty,1,36,265 FROM Product p ";
                sql += "left join MST_WorkCenter W on p.WT=W.WorkCenterID where p.Code+'-'+TNC  NOT IN (Select Code from ITM_Product)AND p.Code+'-'+TNC is not null";
                StringBuilder strSql = new StringBuilder();
                //strSql.Append("SELECT * FROM ITM_ValueForWorkingTimeDetail WHERE ValueForWorkingTimeMasterID='")
                strSql.Append(sql);
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                //return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        // ---Import Routing%
        public void RunScript3()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string sql = "Insert Into ITM_Routing (ProductID,WorkCenterID,LaborRunTime,CalculateTime)";
                sql += "select i.ProductID,VP.WT,CASE VP.Gqty WHEN 0 THEN VP.Times ELSE VP.Times/VP.Gqty END,1 from Product VP INNER JOIN ITM_Product I ON I.Code=VP.Code+'-'+VP.TNC where I.ProductID NOT IN (Select ProductID FROM ITM_Routing)";

                StringBuilder strSql = new StringBuilder();
                //strSql.Append("SELECT * FROM ITM_ValueForWorkingTimeDetail WHERE ValueForWorkingTimeMasterID='")
                strSql.Append(sql);
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                //return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        //---Update Item co duoi LVT, VCNB bang Vat lieu phu, ProductTypeID=3
        public void RunScript4()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string sql = "Update ITM_Product set ProductTypeID=3 where WorkCenterID IN (731,732) And MakeItem=1";
                StringBuilder strSql = new StringBuilder();
                //strSql.Append("SELECT * FROM ITM_ValueForWorkingTimeDetail WHERE ValueForWorkingTimeMasterID='")
                strSql.Append(sql);
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                //return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        //----------- Generate BOM----------------//
        //--Insert BOM
        public void RunScript5()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string sql = "Insert Into ITM_BOM (ProductID,ComponentID,Quantity,BOMUMID) ";
                sql += "Select I1.ProductID,I2.ProductID ComID,1,36 from (select PARENT+'-'+TNC1 Codeparent,CON+'-'+TNC2 CODECOM FROM ";
                sql += "(select b.Parent ,aa1.code TNC1,b.Component Con,aa2.code TNC2 from v_bom b ";
                sql += "inner join aa_nguyencong aa1 on aa1.ID=B.PARENTNC inner join aa_nguyencong AA2 on AA2.ID=B.ComponentNC)yy";
                sql += ") X INNER JOIN ITM_PRODUCT I1 ON I1.CODE=X.Codeparent ";
                sql += "INNER JOIN ITM_PRODUCT I2 ON I2.CODE=X.Codecom where I1.ProductID NOT IN (Select ProductID FROM ITM_BOM)";
                StringBuilder strSql = new StringBuilder();
                //strSql.Append("SELECT * FROM ITM_ValueForWorkingTimeDetail WHERE ValueForWorkingTimeMasterID='")
                strSql.Append(sql);
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                //return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        //----- Insert BOM Nguyenvatlieu
        public void RunScript6()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string sql = "Insert Into ITM_BOM (ProductID,ComponentID,Quantity,BOMUMID) ";
                sql += "Select p1.ProductID,P2.ProductID ComponentID,K.Qty,P2.WeightUMID BOMUMID from ";
                sql += "(";
                sql += "Select X.Code+'-'+NC.Code ProductCode,MaVTHH CodeCom,qty ";
                sql += "FROM(";
                sql += "select Code, MaVTHH,qty/gqty qty,min(NC) NC from product ";
                sql += "where Qty is not null ";
                sql += "group by MaVTHH,Code,qty/gqty)X ";
                sql += "INNER JOIN aa_nguyencong NC ON NC.ID=X.NC";
                sql += ")k ";
                sql += "inner join itm_product P1 on p1.code=k.ProductCode ";
                sql += "inner join itm_product P2 ON P2.Code=k.CodeCom ";
                sql += "WHERE p1.ProductID NOT IN (Select ProductID FROM ITM_BOM) ";
                //sql += "Go";
                StringBuilder strSql = new StringBuilder();
                //strSql.Append("SELECT * FROM ITM_ValueForWorkingTimeDetail WHERE ValueForWorkingTimeMasterID='")
                strSql.Append(sql);
                //strSql.Append("Go");
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                //return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        //--- BOM THEO LevelsS
        public void RunScript7()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string sql = "Insert into ITM_BOM (ComponentID,ProductID,Quantity,BOMUMID)";
                sql += "Select I1.ProductID ComponentID,I2.ProductID ProductID,k.gqty Quantity,36 BOMUMID ";
                sql += "from(select p.Code,p.Levels,p.parrent,isnull(p.gqty,1) gqty,max(p.nc) as nc, x.code as x_code,x.nc as x_nc from product p ";
                sql += "inner join (select Code,Levels,parrent,min(nc)  as nc from product ";
                sql += "Group by Code,Levels,parrent) x on x.Levels=p.parrent ";
                sql += "Group by p.Code,p.Levels,p.parrent,x.code,x.nc,p.gqty)K INNER JOIN aa_nguyencong ANC1 ON ANC1.ID=K.NC ";
                sql += "INNER JOIN aa_nguyencong ANC2 ON ANC2.ID=K.X_NC INNER JOIN ITM_Product I1 ON I1.Code=k.code+'-'+ ANC1.Code ";
                sql += "INNER JOIN ITM_Product I2 ON I2.Code=k.x_code+'-'+ ANC2.Code ";
                sql += "where I2.ProductID NOT IN (Select ProductID FROM ITM_BOM) ";

                sql += "Update ITM_BOM SET BOMUMID= (Select DesignUMID from ITM_Product where ComponentID=ITM_Product.ProductID) ";
                sql += "Update ITM_BOM Set Line=BOMID Drop table aa";
                StringBuilder strSql = new StringBuilder();

                strSql.Append(sql);
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                //return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }


        //-- Đổi chủng loại
        public void RunScript8()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string sql = "Update ITM_Product set Revision='NN',CategoryID=(Select CategoryID From itm_category where Code='NN')WHERE Code like 'NN%'";
                StringBuilder strSql = new StringBuilder();
                //strSql.Append("SELECT * FROM ITM_ValueForWorkingTimeDetail WHERE ValueForWorkingTimeMasterID='")
                strSql.Append(sql);
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "ValueWorkingTimeTable");

                //return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, string.Empty, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, string.Empty, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet GetListKTMaVTHH()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string strSql = ""
                    + " select MaVTHH from AA "
                    + " WHERE MAVTHH not in (select Code from ITM_Product)"
                    + " and MAVTHH IS NOT NULL ";
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "AA");

                return dstPCS;
            }
            catch
            {
                return null;
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet GetListTrungSP()
        {
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {

                string strSql = ""
                   + " select TT, Count(*) FROM ( "
                   + " Select p.Code+'-'+TNC TT,p.Name,'LT' lt,WT wt,1 gg,'2007-09-02 00:00' nn,'2007-09-02 00:00' n1 "
                   + "  FROM Product p "
                   + " left join MST_WorkCenter W on p.WT=W.WorkCenterID)x "
                   + " Group by tt  "
                   + " having count(*)>1 ";
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, "AA");

                return dstPCS;
            }
            catch
            {
                return null;
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
    }
}
